using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using XProject.Entity;
namespace XProject.DAO
{
    class BookShelfDAO:DBAccess
    {
        #region sql command
        string SQL_SELECT_ID = "select max(BookShelfID) from BookShelf";
        string SQL_SELECT_ALL = "select * from BookShelf";
        string SQL_INSERT = "insert into Bookshelf([Name],[Label]) values (@name, @label)";
        string SQL_UPDATE = "update BookShelf set [Name]=@name, [Label]=@label where BookshelfID=@id";
        string SQL_DELETE = "delete from BookShelf where BookshelfID=@id";
        #endregion
        #region field
        string message;
        #endregion    
        #region properties
        public  string  Message
        {
            get { return message; }
        }
        #endregion
        #region constructor
        public BookShelfDAO()
        {
        }
        #endregion
        #region methods
        public IList<BookShelf> GetAll()
        {
            IList<BookShelf> list = new List<BookShelf>();
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_SELECT_ALL, conn);
                if (Connect())
                {
                    OleDbDataReader dr = command.ExecuteReader();

                    while (dr.Read())
                    {
                        BookShelf bs = new BookShelf();
                        bs.BookShelfID = dr.GetInt32(0);
                        bs.Name = dr.GetString(1);
                        bs.Label = dr.GetString(2);
                        list.Add(bs);
                    }
                    Disconnect();
                }
            }
            catch (OleDbException ex)
            {
                message = ex.Message;
            }
            catch (Exception ex) { message = ex.Message; }
            return list;
        }
        public int Insert(BookShelf bs)
        {
            int effectedRows = 0;
            OleDbCommand command = new OleDbCommand(SQL_INSERT,conn);            
            if (Connect())
            {
                command.Parameters.Add("@name", OleDbType.VarChar).Value = bs.Name;
                command.Parameters.Add("@label", OleDbType.VarChar).Value = bs.Label;
                
                effectedRows = command.ExecuteNonQuery();
                command.CommandText = SQL_SELECT_ID;
                bs.BookShelfID = int.Parse(command.ExecuteScalar().ToString());
                Disconnect();
            }
            return effectedRows;
        }
        public int Update(BookShelf bs)
        {
            int effectedRows = 0;
            OleDbCommand command = new OleDbCommand(SQL_UPDATE, conn);
            if (Connect())
            {
                command.Parameters.Add("@name", OleDbType.VarChar).Value = bs.Name;
                command.Parameters.Add("@label", OleDbType.VarChar).Value = bs.Label;
                command.Parameters.Add("@id", OleDbType.Integer).Value = bs.BookShelfID;
                effectedRows = command.ExecuteNonQuery();               
                Disconnect();
            }
            return effectedRows;
        }
        public int Delete(BookShelf bs)
        {
            int effectedRows = 0;
            OleDbCommand command = new OleDbCommand(SQL_DELETE, conn);
            if (Connect())
            {                
                command.Parameters.Add("@id", OleDbType.Integer).Value = bs.BookShelfID;
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
            return effectedRows;
        }
        #endregion
    }
}
